﻿namespace DBUtility
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;

    public abstract class DbHelperSQL : IDisposable
    {
        public static SqlConnection connection = null;
        public static string connectionString = PubConstant.ConnectionString;

        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        public static DataTable ExecDataTable(string SQLString)
        {
            DataTable dataTable = new DataTable();
            try
            {
                try
                {
                    new SqlDataAdapter(SQLString, sqlConn).Fill(dataTable);
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
            }
            finally
            {
            }
            return dataTable;
        }

        public static int ExecScallar(string SQLString)
        {
            int num2;
            using (SqlCommand command = new SqlCommand(SQLString, sqlConn))
            {
                try
                {
                    num2 = Convert.ToInt32(command.ExecuteScalar());
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    command.Dispose();
                }
            }
            return num2;
        }

        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlDataReader reader2;
            SqlConnection sqlConn = DbHelperSQL.sqlConn;
            SqlCommand command = new SqlCommand(strSQL, sqlConn);
            try
            {
                reader2 = command.ExecuteReader();
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            return reader2;
        }

        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlDataReader reader2;
            SqlConnection sqlConn = DbHelperSQL.sqlConn;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, sqlConn, null, SQLString, cmdParms);
                SqlDataReader reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                reader2 = reader;
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            return reader2;
        }

        public static int ExecuteSql(string SQLString)
        {
            int num2;
            using (SqlCommand command = new SqlCommand(SQLString, sqlConn))
            {
                try
                {
                    num2 = command.ExecuteNonQuery();
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    command.Dispose();
                }
            }
            return num2;
        }

        public static int ExecuteSql(string SQLString, string content)
        {
            int num2;
            SqlCommand command = new SqlCommand(SQLString, sqlConn);
            SqlParameter parameter = new SqlParameter("@content", SqlDbType.NText);
            parameter.Value = content;
            command.Parameters.Add(parameter);
            try
            {
                num2 = command.ExecuteNonQuery();
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                command.Dispose();
            }
            return num2;
        }

        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            int num2;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, sqlConn, null, SQLString, cmdParms);
                int num = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                num2 = num;
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
            return num2;
        }

        public static int ExecuteSqlByTime(string SQLString, int Times)
        {
            int num2;
            using (SqlCommand command = new SqlCommand(SQLString, sqlConn))
            {
                try
                {
                    command.CommandTimeout = Times;
                    num2 = command.ExecuteNonQuery();
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    command.Dispose();
                }
            }
            return num2;
        }

        public static object ExecuteSqlGet(string SQLString, string content)
        {
            object obj3;
            SqlCommand command = new SqlCommand(SQLString, sqlConn);
            SqlParameter parameter = new SqlParameter("@content", SqlDbType.NText);
            parameter.Value = content;
            command.Parameters.Add(parameter);
            try
            {
                object objA = command.ExecuteScalar();
                if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                {
                    return null;
                }
                obj3 = objA;
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                command.Dispose();
            }
            return obj3;
        }

        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            int num2;
            SqlCommand command = new SqlCommand(strSQL, sqlConn);
            SqlParameter parameter = new SqlParameter("@fs", SqlDbType.Image);
            parameter.Value = fs;
            command.Parameters.Add(parameter);
            try
            {
                num2 = command.ExecuteNonQuery();
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                command.Dispose();
            }
            return num2;
        }

        public static void ExecuteSqlTran(List<CommandInfo> SQLStringList)
        {
            using (SqlTransaction transaction = sqlConn.BeginTransaction())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        foreach (CommandInfo info in SQLStringList)
                        {
                            string commandText = info.CommandText;
                            SqlParameter[] cmdParms = null;
                            PrepareCommand(command, sqlConn, transaction, commandText, cmdParms);
                            int num = command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static int ExecuteSqlTran(List<string> SQLStringList)
        {
            int num3;
            SqlCommand command = new SqlCommand();
            command.Connection = sqlConn;
            SqlTransaction transaction = sqlConn.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                int num = 0;
                for (int i = 0; i < SQLStringList.Count; i++)
                {
                    string str = SQLStringList[i];
                    if (str.Trim().Length > 1)
                    {
                        command.CommandText = str;
                        num += command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
                num3 = num;
            }
            catch (SqlException exception)
            {
                transaction.Rollback();
                throw new Exception(exception.Message);
            }
            finally
            {
                command.Dispose();
            }
            return num3;
        }

        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = sqlConn;
            SqlTransaction transaction = sqlConn.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                for (int i = 0; i < SQLStringList.Count; i++)
                {
                    string str = SQLStringList[i].ToString();
                    if (str.Trim().Length > 1)
                    {
                        command.CommandText = str;
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }
            catch (SqlException exception)
            {
                transaction.Rollback();
                throw new Exception(exception.Message);
            }
            finally
            {
                command.Dispose();
            }
        }

        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlTransaction transaction = sqlConn.BeginTransaction())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        foreach (DictionaryEntry entry in SQLStringList)
                        {
                            string cmdText = entry.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[]) entry.Value;
                            PrepareCommand(command, sqlConn, transaction, cmdText, cmdParms);
                            int num = command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static void ExecuteSqlTranWithIndentity(List<CommandInfo> SQLStringList)
        {
            using (SqlTransaction transaction = sqlConn.BeginTransaction())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        int num = 0;
                        foreach (CommandInfo info in SQLStringList)
                        {
                            string commandText = info.CommandText;
                            SqlParameter[] cmdParms = null;
                            foreach (SqlParameter parameter in cmdParms)
                            {
                                if (parameter.Direction == ParameterDirection.InputOutput)
                                {
                                    parameter.Value = num;
                                }
                            }
                            PrepareCommand(command, sqlConn, transaction, commandText, cmdParms);
                            int num2 = command.ExecuteNonQuery();
                            foreach (SqlParameter parameter in cmdParms)
                            {
                                if (parameter.Direction == ParameterDirection.Output)
                                {
                                    num = Convert.ToInt32(parameter.Value);
                                }
                            }
                            command.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
        {
            using (SqlTransaction transaction = sqlConn.BeginTransaction())
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        int num = 0;
                        foreach (DictionaryEntry entry in SQLStringList)
                        {
                            string cmdText = entry.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[]) entry.Value;
                            foreach (SqlParameter parameter in cmdParms)
                            {
                                if (parameter.Direction == ParameterDirection.InputOutput)
                                {
                                    parameter.Value = num;
                                }
                            }
                            PrepareCommand(command, sqlConn, transaction, cmdText, cmdParms);
                            int num2 = command.ExecuteNonQuery();
                            foreach (SqlParameter parameter in cmdParms)
                            {
                                if (parameter.Direction == ParameterDirection.Output)
                                {
                                    num = Convert.ToInt32(parameter.Value);
                                }
                            }
                            command.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static bool Exists(string strSql)
        {
            int num;
            object single = GetSingle(strSql);
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }

        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            int num;
            object single = GetSingle(strSql, cmdParms);
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }

        public static int GetMaxID(string FieldName, string TableName)
        {
            object single = GetSingle("select max(" + FieldName + ")+1 from " + TableName);
            if (single == null)
            {
                return 1;
            }
            return int.Parse(single.ToString());
        }

        public static object GetSingle(string SQLString)
        {
            object obj3;
            using (SqlCommand command = new SqlCommand(SQLString, sqlConn))
            {
                try
                {
                    object objA = command.ExecuteScalar();
                    if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                    {
                        return null;
                    }
                    obj3 = objA;
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    command.Dispose();
                }
            }
            return obj3;
        }

        public static object GetSingle(string SQLString, int Times)
        {
            object obj3;
            using (SqlCommand command = new SqlCommand(SQLString, sqlConn))
            {
                try
                {
                    command.CommandTimeout = Times;
                    object objA = command.ExecuteScalar();
                    if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                    {
                        return null;
                    }
                    obj3 = objA;
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    command.Dispose();
                }
            }
            return obj3;
        }

        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            object obj3;
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, sqlConn, null, SQLString, cmdParms);
                object objA = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
                {
                    return null;
                }
                obj3 = objA;
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
            return obj3;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            conn = sqlConn;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        public static DataTable Query(string SQLString)
        {
            DataSet dataSet = new DataSet();
            try
            {
                try
                {
                    new SqlDataAdapter(SQLString, sqlConn).Fill(dataSet, "ds");
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
            }
            finally
            {
            }
            return dataSet.Tables[0];
        }

        public static DataTable Query(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, sqlConn, null, SQLString, cmdParms);
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    try
                    {
                        adapter.Fill(dataSet, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                }
                finally
                {
                }
                return dataSet.Tables[0];
            }
        }

        public static DataTable Query(string SQLString, int Times)
        {
            DataSet dataSet = new DataSet();
            try
            {
                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(SQLString, sqlConn);
                    adapter.SelectCommand.CommandTimeout = Times;
                    adapter.Fill(dataSet, "ds");
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
            }
            finally
            {
            }
            return dataSet.Tables[0];
        }

        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(sqlConn, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteReader();
        }

        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            SqlCommand command = BuildIntCommand(sqlConn, storedProcName, parameters);
            rowsAffected = command.ExecuteNonQuery();
            return (int) command.Parameters["ReturnValue"].Value;
        }

        public static DataTable RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            SqlConnection sqlConn = DbHelperSQL.sqlConn;
            DataSet dataSet = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = BuildQueryCommand(sqlConn, storedProcName, parameters);
            adapter.Fill(dataSet, tableName);
            return dataSet.Tables[0];
        }

        public static DataTable RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            SqlConnection sqlConn = DbHelperSQL.sqlConn;
            DataSet dataSet = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = BuildQueryCommand(sqlConn, storedProcName, parameters);
            adapter.SelectCommand.CommandTimeout = Times;
            adapter.Fill(dataSet, tableName);
            return dataSet.Tables[0];
        }

        void IDisposable.Dispose()
        {
            sqlConn.Close();
        }

        public static bool TabExists(string TableName)
        {
            int num;
            object single = GetSingle("select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1");
            if (object.Equals(single, null) || object.Equals(single, DBNull.Value))
            {
                num = 0;
            }
            else
            {
                num = int.Parse(single.ToString());
            }
            if (num == 0)
            {
                return false;
            }
            return true;
        }

        public static SqlConnection sqlConn
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                    return connection;
                }
                return connection;
            }
        }
    }
}

